﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using FisheryPlatform.Entity;
using FisheryPlatform.DataAccess.DBUtility;
using System.Data;

namespace FisheryPlatform.DataAccess.Term
{
    public class TermsDB
    {
        #region private const
        private const string PARAM_TERM_ID = "@TermId";
        private const string PARAM_NAME = "@Name";
        private const string PARAM_SLUG = "@Slug";
        private const string PARAM_TAXONOMY = "@Taxonomy";
        private const string PARAM_DESCRIPTION = "@Description";
        private const string PARAM_PARENT_ID = "@ParentId";
        private const string PARAM_COUNT = "@Count";

        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        public static void FillEntityFrom(SqlDataReader reader, TermEntity obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.TermId = reader.IsDBNull(reader.GetOrdinal("TermId")) ? 0 : reader.GetInt64(reader.GetOrdinal("TermId"));
                obj.Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? String.Empty : reader.GetString(reader.GetOrdinal("Name"));
                obj.Slug = reader.IsDBNull(reader.GetOrdinal("Slug")) ? String.Empty : reader.GetString(reader.GetOrdinal("Slug"));
                obj.Taxonomy = reader.IsDBNull(reader.GetOrdinal("Taxonomy")) ? TermTaxonomyEnum.Cate : (TermTaxonomyEnum)reader.GetByte(reader.GetOrdinal("Taxonomy"));
                obj.Description = reader.IsDBNull(reader.GetOrdinal("Description")) ? String.Empty : reader.GetString(reader.GetOrdinal("Description"));
                obj.ParentId = reader.IsDBNull(reader.GetOrdinal("ParentId")) ? 0 : reader.GetInt64(reader.GetOrdinal("ParentId"));
                obj.Count = reader.IsDBNull(reader.GetOrdinal("Count")) ? 0 : reader.GetInt32(reader.GetOrdinal("Count"));
            }
            #endregion
        }

        private static SqlParameter[] GetTermsParams(TermEntity obj)
        {
            #region
            SqlParameter[] dbParams ={					
					 SQLHelper.MakeParam(PARAM_TERM_ID, SqlDbType.BigInt,0,obj.TermId),
					 SQLHelper.MakeParam(PARAM_NAME, SqlDbType.NVarChar, 150,obj.Name.Trim()),			
					 SQLHelper.MakeParam(PARAM_SLUG, SqlDbType.NVarChar, 150,obj.Slug),			
					 SQLHelper.MakeParam(PARAM_TAXONOMY, SqlDbType.BigInt,0,(int)obj.Taxonomy),
					 SQLHelper.MakeParam(PARAM_DESCRIPTION, SqlDbType.NVarChar, 400,obj.Description),					
					 SQLHelper.MakeParam(PARAM_PARENT_ID, SqlDbType.BigInt,0,obj.ParentId),
					 SQLHelper.MakeParam(PARAM_COUNT, SqlDbType.Int,0,obj.Count)
			};

            return dbParams;
            #endregion
        }

        public static bool Save(TermEntity obj)
        {
            #region
            try
            {
                SqlParameter[] dbParams = GetTermsParams(obj);

                if (obj.TermId == 0)
                {
                    obj.TermId = Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Terms_InsertUpdate", dbParams));
                    return obj.TermId > 0 ? true : false;
                }
                else
                {
                    var affectNum = SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Terms_InsertUpdate", dbParams);
                    return affectNum > 0 ? true : false;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Terms-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        /// <summary>
        /// 不区分大小写
        /// </summary>
        /// <param name="name"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public static bool ExistName(long termId, string name, long parentId, TermTaxonomyEnum taxonomy)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { 
                    SQLHelper.MakeParam(PARAM_TERM_ID, SqlDbType.BigInt, 0,termId),
                    SQLHelper.MakeParam(PARAM_NAME, SqlDbType.NVarChar, 150, name.Trim()),
                    SQLHelper.MakeParam(PARAM_PARENT_ID, SqlDbType.BigInt, 0,parentId),
                    SQLHelper.MakeParam(PARAM_TAXONOMY, SqlDbType.TinyInt, 0,(int) taxonomy)
                };

                var result = SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Terms_ExistName", dbParams);
                if (int.Parse(result.ToString()) == 1)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Terms-->ExistName-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static List<TermEntity> GetListByTermIds(string termIds)
        {
            #region
            termIds = termIds.TrimEnd(',');
            if (string.IsNullOrEmpty(termIds))
            {
                return new List<TermEntity>();
            }
            else
            {
                var pageCount = 0;
                return Search(" termId in(" + termIds + ")", 0, 0, out pageCount);
            }
            #endregion
        }

        public static List<TermEntity> Search(string sqlWhere, int pageIndex, int pageSize, out int pageCount)
        {
            #region
            List<TermEntity> result = new List<TermEntity>();
            pageCount = 0;
            try
            {
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " 1=1 ";
                }

                SqlParameter[] dbParams =
                {
                    SQLHelper.MakeParam("@sqlWhere", SqlDbType.NVarChar,0,sqlWhere),
                    SQLHelper.MakeParam("@pageIndex",SqlDbType.Int,0,pageIndex),
                    SQLHelper.MakeParam("@pageSize",SqlDbType.Int,0,pageSize),
                    SQLHelper.MakeOutParam("@pageCount", SqlDbType.Int,0,0)                    
                };
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Terms_Search", dbParams))
                {
                    while (reader.Read())
                    {
                        TermEntity model = new TermEntity();
                        FillEntityFrom(reader, model);
                        result.Add(model);
                    }
                }
                pageCount = int.Parse(dbParams[3].Value.ToString());
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Terms-->Search-->" + e.ToString());
                ExceptionMessageDB.Record(ex);
            }
            return result;
            #endregion
        }

        public static bool DeleteByTermId(long termId)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_TERM_ID, SqlDbType.BigInt, 0, termId) };
                var resultValue = SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Terms_DeleteByTermId", dbParams);
                return resultValue.ToString() == "1" ? true : false;
            }

            catch (Exception e)
            {
                Exception ex = new Exception("Terms-->DeleteByTermId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static TermEntity GetByTermId(long termId)
        {
            #region
            SqlDataReader reader = null;
            TermEntity obj = new TermEntity();

            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_TERM_ID, SqlDbType.BigInt, 0, termId) };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Terms_GetByTermId", dbParams);

                if (reader.Read())
                {
                    FillEntityFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Terms-->GetByTermId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

    }
}
